{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/P1-NL2SQL/6_3_AWS_Bedrock_NL2SQL_Client.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Paa52inqag9w"
   },
   "source": [
    "<div>\n",
    "    <h1>Large Language Models Projects</a></h1>\n",
    "    <h3>Apply and Implement Strategies for Large Language Models</h3>\n",
    "    <h2>6.3-Calling AWS Bedrock from Python. </h2>\n",
    "    <h3></h3>\n",
    "</div>\n",
    "\n",
    "by [Pere Martra](https://www.linkedin.com/in/pere-martra/)\n",
    "__________\n",
    "Models: llama3-8b-instruct-v1\n",
    "\n",
    "Colab Environment: CPU\n",
    "\n",
    "Keys:\n",
    "* BedRock\n",
    "* AWS\n",
    "* NL2SQL\n",
    "\n",
    "__________"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uxFVaxlskUf-"
   },
   "source": [
    "In this notebook, we make a call to a Model from AWS Bedrock  that we've set up to work as a translator for SQL queries from natural language.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "PQmhudeOj9PJ",
    "outputId": "a4d6a38c-90fb-446a-c0bb-c6a2695f8ff5"
   },
   "outputs": [],
   "source": [
    "pip install -q boto3==1.34.108"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Jssm3zDHbKq-"
   },
   "outputs": [],
   "source": [
    "import boto3\n",
    "import json\n",
    "from getpass import getpass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "UeZIlGHbkfHL",
    "outputId": "d7b591a7-a462-4b2f-cce2-70ca0e30e9c4"
   },
   "outputs": [],
   "source": [
    "aws_access_key_id = getpass('AWS Acces key: ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "LjHrlQ3IkfVw",
    "outputId": "15023f96-056a-495a-8049-3a8d101578a4"
   },
   "outputs": [],
   "source": [
    "aws_secret_access_key = getpass('AWS Secret Key: ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "B2C_0LNjHFo3"
   },
   "outputs": [],
   "source": [
    "client = boto3.client(\"bedrock-runtime\",\n",
    "                      region_name=\"us-west-2\",\n",
    "                      aws_access_key_id = aws_access_key_id,\n",
    "                      aws_secret_access_key= aws_secret_access_key)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Ua4SgAVYwrVF"
   },
   "outputs": [],
   "source": [
    "# Set the model ID, e.g., Llama 3 8B Instruct.\n",
    "model_id = \"meta.llama3-8b-instruct-v1:0\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "YMVK994yhbnP"
   },
   "outputs": [],
   "source": [
    "# Define the user message to send.\n",
    "user_message = \"What is the name of the best paid employee?\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "BW89l6qdPEUy"
   },
   "outputs": [],
   "source": [
    "model_instructions = \"\"\"\n",
    "Your task is to convert a question into a SQL query, given a SQL database schema.\n",
    "Adhere to these rules:\n",
    "- **Deliberately go through the question and database schema word by word to appropriately answer the question.\n",
    "- **Return Only SQL Code.\n",
    "   ### Input\n",
    "   Generate a SQL query that answers the question below.\n",
    "   This query will run on a database whose schema is represented in this string:\n",
    "\n",
    "   create table employees(\n",
    "       ID_Usr INT primary key,-- Unique Id for employee\n",
    "       name VARCHAR -- Name of employee\n",
    "       );\n",
    "\n",
    "   create table salary(\n",
    "       ID_Usr INT,-- Unique Id for employee\n",
    "       year DATE, -- Date\n",
    "       salary FLOAT, --Salary of employee\n",
    "       foreign key (ID_Usr) references employees(ID_Usr) -- Join Employees with salary\n",
    "       );\n",
    "\n",
    "   create table studies(\n",
    "       ID_study INT, -- Unique ID study\n",
    "       ID_Usr INT, -- ID employee\n",
    "       educational_level INT,  -- 5=phd, 4=Master, 3=Bachelor\n",
    "       Institution VARCHAR, --Name of instituon where eployee studied\n",
    "       Years DATE, -- Date acomplishement stdy\n",
    "       Speciality VARCHAR, -- Speciality of studies\n",
    "       primary key (ID_study, ID_Usr), --Primary Key ID_Usr + ID_Study\n",
    "       foreign key(ID_Usr) references employees (ID_Usr)\n",
    "       );\n",
    "\n",
    "\"\"\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "9yc2edu10CbQ"
   },
   "outputs": [],
   "source": [
    "# Embed the message in Llama 3's prompt format.\n",
    "prompt = f\"\"\"\n",
    "<|begin_of_text|>\n",
    "<|start_header_id|>system<|end_header_id|>\n",
    "{model_instructions}\n",
    "<|eot_id|>\n",
    "<|start_header_id|>user<|end_header_id|>\n",
    "{user_message}\n",
    "<|eot_id|>\n",
    "<|start_header_id|>assistant<|end_header_id|>\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "RE5bDGz8PEUy",
    "outputId": "9ecd694b-c1b7-4217-9c3f-58aae49dd7e6"
   },
   "outputs": [],
   "source": [
    "print (prompt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tKCmd3S90KET"
   },
   "outputs": [],
   "source": [
    "# Format the request payload using the model's native structure.\n",
    "hyper = {\n",
    "    \"prompt\": prompt,\n",
    "    # Optional inference parameters:\n",
    "    \"max_gen_len\": 512,\n",
    "    \"temperature\": 0.0\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "xnkNtdbA0Lv2"
   },
   "outputs": [],
   "source": [
    "# Encode and send the request.\n",
    "response = client.invoke_model(body=json.dumps(hyper), modelId=model_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CZfzGp6e0Pfd"
   },
   "outputs": [],
   "source": [
    "# Decode the native response body.\n",
    "model_response = json.loads(response[\"body\"].read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_HAm1LgD1-af",
    "outputId": "44731d22-5e8e-466d-a245-c602ede1bcd9"
   },
   "outputs": [],
   "source": [
    "# Extract and print the generated text.\n",
    "response_text = model_response[\"generation\"]\n",
    "print(response_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tARgaG9l2AG8"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
